# #!/usr/bin/env python
# # -*- encoding: utf-8 -*-
# '''
# @File    :   c2b_config_utils.py
# @Contact :   pengwei.sun@aihuishou.com
# @License :   (C)Copyright aihuishou
#
# @Modify Time      @Author       @Version    @Desciption
# ------------      -----------   --------    -----------
# 2022-10-19 11:34   pengwei.sun      1.0         None
# '''
#
# from src.utils.db_processor import presto_processor
#
#
# query_process_sql = """
# select a.*,b.item_quotation_price_num_media as item_quotation_price_num_media_product,b.cnt as cnt_product
# from
# (
# select product_id,product_name,secondary_level_id,secondary_level_name,memory,storage,
# purchase_way ,guarantee ,
# avg(item_quotation_price_num) as item_quotation_price_num_avg,
# min(item_quotation_price_num) as item_quotation_price_num_min,
# max(item_quotation_price_num) as item_quotation_price_num_max,
# approx_percentile(item_quotation_price_num,0.5) item_quotation_price_num_media,
# count(1) as cnt
# from
# (
# SELECT
# cdp.document_item_id,
# cast(psdp.settle_amount_num as int)AS item_quotation_price_num,
#  cast(psdp.settle_document_create_date as date) as settle_list_create_date,
# cast(cdp.create_date as date) AS shop_out_date,
# cast(dp.product_id as int) product_id,
# trim(dp.product_name) as product_name,
# cast(dp.product_brand_id  as int) as brand_id,
# trim(dp.product_brand_name) as product_brand_name,
# cast(cdp.product_combine_level_id  as int) as level_id,
# trim(cdp.product_combine_level_name) AS product_level_name,
# sku2level.secondary_level_id,
# level.product_level_name  AS secondary_level_name,
# cdp.product_no,
# dpk.memory_name as memory ,
# dpk.memory_name as storage ,
# dpk.color_name as body_color ,
# dpk.small_model_name as small_version ,
# dpk.purchase_channel_name as purchase_way ,
# dpk.network_standard_name as guarantee ,
# dpk.network_standard_name as networks
#
# FROM dw.dw_centre_document_product cdp
#  JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no
# inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no
# JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
# inner join dim.dim_product_sku dpk
# on sku2level.secondary_sku_id=dpk.product_sku_id
# inner join dim.dim_product_level level
# on level.product_level_id=sku2level.secondary_level_id
# JOIN ods.ods_algorithm_dm_model_goods_code_property_mobile cpm  ON psdp.goods_code = cpm.goods_code
# left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no
# AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
# WHERE dp.product_category_id = 1 AND cdp.document_category_id >= 200
# AND cdp.document_item_status_id IN (201, 202, 204)
# AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
# AND psdp.quotation_document_type_id = 10
# AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
# and psdp.settle_status_id not in (-1,5,9)
# and substr(level.product_level_name,1,1) in ('A','B','C')
# and dp.product_brand_id !=52
# and psdp.product_source_id in (101,103)
# and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
# and dp.product_id in (31767, 39143, 37600, 34097, 23906, 39004, 35515, 34688, 39025, 36687, 36248, 33498, 32850, 39165, 37524, 35622,
# 35164, 38972, 36247, 23866, 36045, 33009, 39177, 37703, 34402, 34053, 37109, 37557, 35557, 34352, 39064, 36300, 37598, 34694, 34086, 27551,
#  35941, 35884, 36047, 36046, 26422, 27784, 37330, 34397, 33012, 42861, 35623, 36439, 28421, 30175, 22706, 37680, 35480, 37051, 35963, 20692,
#   35100, 37650, 34748, 17425, 38297, 27877, 34464, 29116, 34403, 37512, 24390, 22441, 17527, 36246, 27822, 36939, 37589, 28760, 35106,
#   38394, 37217, 35700, 34687, 35290, 29637, 28121, 2246,2247,17455,17457,17460,17458,17459,20079,34701,43512,43513,43511,43510,
#   39142, 39087, 38660, 38564, 38257, 37677, 37676, 37664, 37627, 37604, 37519, 37376,
#   37094, 37019, 36985, 36805, 36744, 36708, 36510, 36493, 36249, 36095, 36044, 35585, 35584, 35547, 35246, 34948, 34809, 34756, 34755,
#   34754, 34753, 34708, 34576, 34575, 34504, 34401, 34324, 34286, 34164, 33468, 33281, 32955, 32835, 32588, 32461, 32460, 32292, 32291,
#   32290, 32068, 32066, 32050, 31945, 31008, 30173, 30044, 29705, 29693, 29607, 29291, 29261, 29260, 29115, 29023, 29002, 29000, 28999,
#   28968, 28829, 28612, 28495, 28168, 27860, 27859, 27782, 27781, 27640, 27639, 27637, 27627, 27550, 27512, 27344, 27301, 27280, 27253,
#   27036, 26959, 26913, 26912, 26707, 26613, 26536, 26512, 26491, 26467, 26464, 26431, 26428, 26381, 26170, 26102, 26099, 26078, 25998,
#   25950, 25827, 25820, 25806, 25731, 25680, 25679, 25677, 25676, 25519, 25240, 25170, 25097, 24799, 24348, 24347, 24240, 24217, 23819,
#   23788, 23756, 23668, 23640, 23423, 23422, 23049, 20421, 20122, 19664, 17895, 17752, 17726, 17462, 17461, 15080,
#   9478, 15080, 17252, 17338, 17507, 17578, 17887, 17895, 17896, 19664, 19665, 20122, 20421, 22187, 22441, 22706, 23049, 23077, 23125,
#    23534, 23587, 23614, 23640, 23668, 23760, 23788, 23814, 23819, 23862, 23866, 24217, 24240, 24390, 24992, 25231, 25432, 25519, 25641,
#     25682, 25820, 25985, 26062, 26129, 26165, 26167, 26170, 26422, 26490, 26491, 26493, 26513, 26662, 27035, 27036, 27541, 28760, 29348,
#     36744, 29260, 21885, 23841, 24552, 26808, 27253, 36246, 32955, 17698, 26098, 34576, 26912, 32588, 35884, 30173, 28495, 20499, 25616,
#     25765, 34756, 35762, 27344, 34754, 20751, 21769, 22117, 32827, 34376, 32835, 27550, 36633, 23375, 25400, 25422, 26078, 26536, 27512,
#     27782, 29637, 27860, 27781, 34286, 28614, 26431, 19657, 23968, 26671, 29261, 23756, 28168, 34053, 24348, 32244, 26613, 28829, 27980,
#     26197, 29003, 32089, 34755, 27301, 29117, 35290, 32890, 27600, 34948, 25232, 19534, 26913, 30175, 17304, 19343, 23865, 24239, 29116,
#     31008, 32066, 35547, 36784, 24347, 34590, 32460, 32461, 27549, 27627, 26464, 25097, 34575, 27280, 33280, 17616, 17967, 25401, 27859,
#     28779, 29693, 36510, 26959, 33416, 29118, 28612, 27764, 20173, 20174, 25239, 25731, 26099, 36249, 26467, 26492, 29263, 27602, 27110,
#     28502, 25806, 29443, 32068, 34164, 34753, 26102, 29291, 36628, 17795, 21036, 24993, 29836, 35164, 39142, 29222, 26428, 27822, 35963,
#     36493, 20204, 24232, 32581, 34504, 36804, 34687, 25950, 26958, 32019, 24799, 25170, 26476, 27877, 37018, 37205, 34686, 30044, 34096,
#     26707, 26266, 27995, 33009, 38676, 37066, 29023, 33415, 36610, 20606, 20742, 23346, 25240, 29705, 32850, 35271, 35163, 34574, 37519,
#     35564, 28480, 17657, 23257, 24349, 25998, 27643, 28121, 32022, 35044, 36687, 33194, 36394, 37376, 37676, 47987, 25676, 35100, 24996,
#     27861, 28999, 29208, 31007, 29246, 30174, 33007, 27170, 32884, 35585, 31445, 34748, 17793, 25677, 28253, 34808, 27646, 23906, 34809,
#     27006, 33436, 34054, 35006, 14990, 17259, 17912, 20692, 23460, 23619, 23797, 24995, 25948, 26911, 26927, 28443, 36708, 38660, 17746,
#     20144, 32716, 35322, 39087, 38600, 35584, 27908, 29666, 35197, 26512, 34324, 29002, 37077, 37677, 38257, 39001, 29777, 36749, 27551,
#     29807, 33498, 37600, 25303, 27893, 28968, 38564, 31097, 20658, 22440, 27270, 32988, 35527, 39141, 37589, 17669, 26780, 30384,
#     35349, 24966, 35106, 34086, 23535, 35246, 33468, 34287, 33281, 34734, 34097, 29115, 17325, 31945, 36250, 28525, 36805, 14972,
#     29706, 29937, 36291, 27190, 36101, 17489, 27297, 29366, 32528, 36093, 36398, 17337, 20603, 23758, 29500, 27783, 35830, 17488,
#     21581, 30602, 37687, 29607, 27793, 35809, 23891, 34795, 17425, 18030, 23281, 33363, 34401, 34709, 27882, 27011, 26380, 17886,
#     22115, 36789, 17521, 17903, 32472, 37019, 38696, 39143, 32972, 36248, 35623, 34598, 34448, 37627, 17321, 17345, 17493, 17614,
#     17712, 17857, 17869, 36985, 23776, 25752, 27334, 29413, 36462, 29845, 34431, 17579, 20618, 33012, 29534, 37487, 34397, 37330,
#     25853, 17647, 17988, 29000, 26809, 34403, 30985, 27756, 35451, 17615, 24497, 34733, 25040, 37377, 17610, 23289, 23613, 34365,
#     35582, 17611, 28466, 35008, 34708, 37596, 37650, 32055, 37604, 36677, 30385, 36939, 19533, 42701, 26001, 34896, 35941, 37664, 38584,
#     30423, 34730, 9208, 17320, 23740, 26899, 34443, 26783, 35005, 37679, 20092, 32634, 32821, 37535, 29135, 42519, 37051, 38884, 42595,
#     27778, 32632, 36692, 34526, 32958, 25521, 38250, 34402, 39165, 24341, 26716, 27405, 31440, 17486, 25717, 28684, 33425, 20143, 27693,
#     43241, 42705, 34405, 23286, 17527, 17686, 31567, 31550, 31767, 42861, 34897, 43692, 27372, 27784, 35671, 36494, 38630, 39236, 32872,
#     34464, 37094, 17787, 35007, 37680, 27491, 34688, 43220, 20418, 28625, 36300, 36352, 10025, 17494, 17713, 36095, 28566, 26027, 17733, 27988,
#     35699, 15054, 17875, 23764, 26381, 28478, 32511, 37581,66428,
#     38959, 43695, 54816, 37656, 37255, 35833, 43408, 23820, 43693, 76793, 20210, 55383, 42879, 42880, 42860, 57425, 52555, 57426, 43349, 32776, 35181, 52548, 52379, 58585, 43694, 38329)
# )a
# group by product_id,product_name,secondary_level_id,secondary_level_name,memory,storage,
# purchase_way ,guarantee
# )a
# left join algo.algo_product_price_segment_data b
# on a.product_id=b.product_id and a.storage=b.storage
# """
# #获取型号 等级，ppv组合的售卖量信息
# query_ppv_combine_sql="""
#
# select
# product_id,product_name,secondary_level_id,
# storage,purchase_way,guarantee,count(1) as cnt
# from
# (
# SELECT
# cdp.document_item_id,
# cast(psdp.settle_amount_num as int)AS item_quotation_price_num,
#  cast(psdp.settle_document_create_date as date) as settle_list_create_date,
# cast(cdp.create_date as date) AS shop_out_date,
# cast(dp.product_id as int) product_id,
# trim(dp.product_name) as product_name,
# cast(dp.product_brand_id  as int) as brand_id,
# trim(dp.product_brand_name) as product_brand_name,
# cast(cdp.product_combine_level_id  as int) as level_id,
# trim(cdp.product_combine_level_name) AS product_level_name,
# sku2level.secondary_level_id,
# level.product_level_name  AS secondary_level_name,
# cdp.product_no,
# dpk.memory_name as memory ,
# dpk.memory_name as storage ,
# dpk.color_name as body_color ,
# dpk.small_model_name as small_version ,
# dpk.purchase_channel_name as purchase_way ,
# dpk.network_standard_name as guarantee ,
# dpk.network_standard_name as networks
#
# FROM dw.dw_centre_document_product cdp
#  JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no
# inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no
# JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
# inner join dim.dim_product_sku dpk
# on sku2level.secondary_sku_id=dpk.product_sku_id
# inner join dim.dim_product_level level
# on level.product_level_id=sku2level.secondary_level_id
# JOIN ods.ods_algorithm_dm_model_goods_code_property_mobile cpm  ON psdp.goods_code = cpm.goods_code
# left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no
# AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
# WHERE dp.product_category_id = 1 AND cdp.document_category_id >= 200
# AND cdp.document_item_status_id IN (201, 202, 204)
# AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
# AND psdp.quotation_document_type_id = 10
# AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
# and psdp.settle_status_id not in (-1,5,9)
# and substr(level.product_level_name,1,1) in ('A','B','C')
# and dp.product_brand_id !=52
# and psdp.product_source_id in (101,103)
# and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
# and dp.product_id in (31767, 39143, 37600, 34097, 23906, 39004, 35515, 34688, 39025, 36687, 36248, 33498, 32850, 39165, 37524, 35622,
# 35164, 38972, 36247, 23866, 36045, 33009, 39177, 37703, 34402, 34053, 37109, 37557, 35557, 34352, 39064, 36300, 37598, 34694, 34086, 27551,
#  35941, 35884, 36047, 36046, 26422, 27784, 37330, 34397, 33012, 42861, 35623, 36439, 28421, 30175, 22706, 37680, 35480, 37051, 35963, 20692,
#   35100, 37650, 34748, 17425, 38297, 27877, 34464, 29116, 34403, 37512, 24390, 22441, 17527, 36246, 27822, 36939, 37589, 28760, 35106,
#   38394, 37217, 35700, 34687, 35290, 29637, 28121, 2246,2247,17455,17457,17460,17458,17459,20079,34701,43512,43513,43511,43510,
#   39142, 39087, 38660, 38564, 38257, 37677, 37676, 37664, 37627, 37604, 37519, 37376,
#   37094, 37019, 36985, 36805, 36744, 36708, 36510, 36493, 36249, 36095, 36044, 35585, 35584, 35547, 35246, 34948, 34809, 34756, 34755,
#   34754, 34753, 34708, 34576, 34575, 34504, 34401, 34324, 34286, 34164, 33468, 33281, 32955, 32835, 32588, 32461, 32460, 32292, 32291,
#   32290, 32068, 32066, 32050, 31945, 31008, 30173, 30044, 29705, 29693, 29607, 29291, 29261, 29260, 29115, 29023, 29002, 29000, 28999,
#   28968, 28829, 28612, 28495, 28168, 27860, 27859, 27782, 27781, 27640, 27639, 27637, 27627, 27550, 27512, 27344, 27301, 27280, 27253,
#   27036, 26959, 26913, 26912, 26707, 26613, 26536, 26512, 26491, 26467, 26464, 26431, 26428, 26381, 26170, 26102, 26099, 26078, 25998,
#   25950, 25827, 25820, 25806, 25731, 25680, 25679, 25677, 25676, 25519, 25240, 25170, 25097, 24799, 24348, 24347, 24240, 24217, 23819,
#   23788, 23756, 23668, 23640, 23423, 23422, 23049, 20421, 20122, 19664, 17895, 17752, 17726, 17462, 17461, 15080,
#   9478, 15080, 17252, 17338, 17507, 17578, 17887, 17895, 17896, 19664, 19665, 20122, 20421, 22187, 22441, 22706, 23049, 23077, 23125,
#    23534, 23587, 23614, 23640, 23668, 23760, 23788, 23814, 23819, 23862, 23866, 24217, 24240, 24390, 24992, 25231, 25432, 25519, 25641,
#     25682, 25820, 25985, 26062, 26129, 26165, 26167, 26170, 26422, 26490, 26491, 26493, 26513, 26662, 27035, 27036, 27541, 28760, 29348,
#     36744, 29260, 21885, 23841, 24552, 26808, 27253, 36246, 32955, 17698, 26098, 34576, 26912, 32588, 35884, 30173, 28495, 20499, 25616,
#     25765, 34756, 35762, 27344, 34754, 20751, 21769, 22117, 32827, 34376, 32835, 27550, 36633, 23375, 25400, 25422, 26078, 26536, 27512,
#     27782, 29637, 27860, 27781, 34286, 28614, 26431, 19657, 23968, 26671, 29261, 23756, 28168, 34053, 24348, 32244, 26613, 28829, 27980,
#     26197, 29003, 32089, 34755, 27301, 29117, 35290, 32890, 27600, 34948, 25232, 19534, 26913, 30175, 17304, 19343, 23865, 24239, 29116,
#     31008, 32066, 35547, 36784, 24347, 34590, 32460, 32461, 27549, 27627, 26464, 25097, 34575, 27280, 33280, 17616, 17967, 25401, 27859,
#     28779, 29693, 36510, 26959, 33416, 29118, 28612, 27764, 20173, 20174, 25239, 25731, 26099, 36249, 26467, 26492, 29263, 27602, 27110,
#     28502, 25806, 29443, 32068, 34164, 34753, 26102, 29291, 36628, 17795, 21036, 24993, 29836, 35164, 39142, 29222, 26428, 27822, 35963,
#     36493, 20204, 24232, 32581, 34504, 36804, 34687, 25950, 26958, 32019, 24799, 25170, 26476, 27877, 37018, 37205, 34686, 30044, 34096,
#     26707, 26266, 27995, 33009, 38676, 37066, 29023, 33415, 36610, 20606, 20742, 23346, 25240, 29705, 32850, 35271, 35163, 34574, 37519,
#     35564, 28480, 17657, 23257, 24349, 25998, 27643, 28121, 32022, 35044, 36687, 33194, 36394, 37376, 37676, 47987, 25676, 35100, 24996,
#     27861, 28999, 29208, 31007, 29246, 30174, 33007, 27170, 32884, 35585, 31445, 34748, 17793, 25677, 28253, 34808, 27646, 23906, 34809,
#     27006, 33436, 34054, 35006, 14990, 17259, 17912, 20692, 23460, 23619, 23797, 24995, 25948, 26911, 26927, 28443, 36708, 38660, 17746,
#     20144, 32716, 35322, 39087, 38600, 35584, 27908, 29666, 35197, 26512, 34324, 29002, 37077, 37677, 38257, 39001, 29777, 36749, 27551,
#     29807, 33498, 37600, 25303, 27893, 28968, 38564, 31097, 20658, 22440, 27270, 32988, 35527, 39141, 37589, 17669, 26780, 30384,
#     35349, 24966, 35106, 34086, 23535, 35246, 33468, 34287, 33281, 34734, 34097, 29115, 17325, 31945, 36250, 28525, 36805, 14972,
#     29706, 29937, 36291, 27190, 36101, 17489, 27297, 29366, 32528, 36093, 36398, 17337, 20603, 23758, 29500, 27783, 35830, 17488,
#     21581, 30602, 37687, 29607, 27793, 35809, 23891, 34795, 17425, 18030, 23281, 33363, 34401, 34709, 27882, 27011, 26380, 17886,
#     22115, 36789, 17521, 17903, 32472, 37019, 38696, 39143, 32972, 36248, 35623, 34598, 34448, 37627, 17321, 17345, 17493, 17614,
#     17712, 17857, 17869, 36985, 23776, 25752, 27334, 29413, 36462, 29845, 34431, 17579, 20618, 33012, 29534, 37487, 34397, 37330,
#     25853, 17647, 17988, 29000, 26809, 34403, 30985, 27756, 35451, 17615, 24497, 34733, 25040, 37377, 17610, 23289, 23613, 34365,
#     35582, 17611, 28466, 35008, 34708, 37596, 37650, 32055, 37604, 36677, 30385, 36939, 19533, 42701, 26001, 34896, 35941, 37664, 38584,
#     30423, 34730, 9208, 17320, 23740, 26899, 34443, 26783, 35005, 37679, 20092, 32634, 32821, 37535, 29135, 42519, 37051, 38884, 42595,
#     27778, 32632, 36692, 34526, 32958, 25521, 38250, 34402, 39165, 24341, 26716, 27405, 31440, 17486, 25717, 28684, 33425, 20143, 27693,
#     43241, 42705, 34405, 23286, 17527, 17686, 31567, 31550, 31767, 42861, 34897, 43692, 27372, 27784, 35671, 36494, 38630, 39236, 32872,
#     34464, 37094, 17787, 35007, 37680, 27491, 34688, 43220, 20418, 28625, 36300, 36352, 10025, 17494, 17713, 36095, 28566, 26027, 17733, 27988,
#     35699, 15054, 17875, 23764, 26381, 28478, 32511, 37581,66428,
#     38959, 43695, 54816, 37656, 37255, 35833, 43408, 23820, 43693, 76793, 20210, 55383, 42879, 42880, 42860, 57425, 52555, 57426, 43349, 32776, 35181, 52548, 52379, 58585, 43694, 38329)
#   )a
# group by 1,2,3,4,5,6
#
# """
# #获取ppv打分和rank数据
# query_ppv_rank_sql = """
#   select distinct column_name,column_value_name,ppv_rank, ppv_rank as low_ppv_rank from algo.algo_c2b_ppv_rank_android_extend
#  """
#
# #获取型号维度的ppv组合的出货数量
# query_product_ppv_cnt="""
# select a.*,b.item_quotation_price_num_media as item_quotation_price_num_media_product,b.cnt as cnt_product
# from
# (
# select product_id,product_name,storage,
# purchase_way ,guarantee ,
# avg(item_quotation_price_num) as item_quotation_price_num_avg,
# min(item_quotation_price_num) as item_quotation_price_num_min,
# max(item_quotation_price_num) as item_quotation_price_num_max,
# approx_percentile(item_quotation_price_num,0.5) item_quotation_price_num_media,
# count(1) as cnt
# from
# (
# SELECT
# cdp.document_item_id,
# cast(psdp.settle_amount_num as int)AS item_quotation_price_num,
#  cast(psdp.settle_document_create_date as date) as settle_list_create_date,
# cast(cdp.create_date as date) AS shop_out_date,
# cast(dp.product_id as int) product_id,
# trim(dp.product_name) as product_name,
# cast(dp.product_brand_id  as int) as brand_id,
# trim(dp.product_brand_name) as product_brand_name,
# cast(cdp.product_combine_level_id  as int) as level_id,
# trim(cdp.product_combine_level_name) AS product_level_name,
# sku2level.secondary_level_id,
# level.product_level_name  AS secondary_level_name,
# cdp.product_no,
# dpk.memory_name as memory ,
# dpk.memory_name as storage ,
# dpk.color_name as body_color ,
# dpk.small_model_name as small_version ,
# dpk.purchase_channel_name as purchase_way ,
# dpk.network_standard_name as guarantee ,
# dpk.network_standard_name as networks
#
# FROM dw.dw_centre_document_product cdp
#  JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no
# inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no
# JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
# inner join dim.dim_product_sku dpk
# on sku2level.secondary_sku_id=dpk.product_sku_id
# inner join dim.dim_product_level level
# on level.product_level_id=sku2level.secondary_level_id
# JOIN ods.ods_algorithm_dm_model_goods_code_property_mobile cpm  ON psdp.goods_code = cpm.goods_code
# left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no
# AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
# WHERE dp.product_category_id = 1 AND cdp.document_category_id >= 200
# AND cdp.document_item_status_id IN (201, 202, 204)
# AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
# AND psdp.quotation_document_type_id = 10
# AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
# and psdp.settle_status_id not in (-1,5,9)
# and substr(level.product_level_name,1,1) in ('A','B','C')
# and dp.product_brand_id !=52
# and psdp.product_source_id in (101,103)
# and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
# and dp.product_id in (31767, 39143, 37600, 34097, 23906, 39004, 35515, 34688, 39025, 36687, 36248, 33498, 32850, 39165, 37524, 35622,
# 35164, 38972, 36247, 23866, 36045, 33009, 39177, 37703, 34402, 34053, 37109, 37557, 35557, 34352, 39064, 36300, 37598, 34694, 34086, 27551,
#  35941, 35884, 36047, 36046, 26422, 27784, 37330, 34397, 33012, 42861, 35623, 36439, 28421, 30175, 22706, 37680, 35480, 37051, 35963, 20692,
#   35100, 37650, 34748, 17425, 38297, 27877, 34464, 29116, 34403, 37512, 24390, 22441, 17527, 36246, 27822, 36939, 37589, 28760, 35106,
#   38394, 37217, 35700, 34687, 35290, 29637, 28121, 2246,2247,17455,17457,17460,17458,17459,20079,34701,43512,43513,43511,43510,
#   39142, 39087, 38660, 38564, 38257, 37677, 37676, 37664, 37627, 37604, 37519, 37376,
#   37094, 37019, 36985, 36805, 36744, 36708, 36510, 36493, 36249, 36095, 36044, 35585, 35584, 35547, 35246, 34948, 34809, 34756, 34755,
#   34754, 34753, 34708, 34576, 34575, 34504, 34401, 34324, 34286, 34164, 33468, 33281, 32955, 32835, 32588, 32461, 32460, 32292, 32291,
#   32290, 32068, 32066, 32050, 31945, 31008, 30173, 30044, 29705, 29693, 29607, 29291, 29261, 29260, 29115, 29023, 29002, 29000, 28999,
#   28968, 28829, 28612, 28495, 28168, 27860, 27859, 27782, 27781, 27640, 27639, 27637, 27627, 27550, 27512, 27344, 27301, 27280, 27253,
#   27036, 26959, 26913, 26912, 26707, 26613, 26536, 26512, 26491, 26467, 26464, 26431, 26428, 26381, 26170, 26102, 26099, 26078, 25998,
#   25950, 25827, 25820, 25806, 25731, 25680, 25679, 25677, 25676, 25519, 25240, 25170, 25097, 24799, 24348, 24347, 24240, 24217, 23819,
#   23788, 23756, 23668, 23640, 23423, 23422, 23049, 20421, 20122, 19664, 17895, 17752, 17726, 17462, 17461, 15080,
#   9478, 15080, 17252, 17338, 17507, 17578, 17887, 17895, 17896, 19664, 19665, 20122, 20421, 22187, 22441, 22706, 23049, 23077, 23125,
#    23534, 23587, 23614, 23640, 23668, 23760, 23788, 23814, 23819, 23862, 23866, 24217, 24240, 24390, 24992, 25231, 25432, 25519, 25641,
#     25682, 25820, 25985, 26062, 26129, 26165, 26167, 26170, 26422, 26490, 26491, 26493, 26513, 26662, 27035, 27036, 27541, 28760, 29348,
#     36744, 29260, 21885, 23841, 24552, 26808, 27253, 36246, 32955, 17698, 26098, 34576, 26912, 32588, 35884, 30173, 28495, 20499, 25616,
#     25765, 34756, 35762, 27344, 34754, 20751, 21769, 22117, 32827, 34376, 32835, 27550, 36633, 23375, 25400, 25422, 26078, 26536, 27512,
#     27782, 29637, 27860, 27781, 34286, 28614, 26431, 19657, 23968, 26671, 29261, 23756, 28168, 34053, 24348, 32244, 26613, 28829, 27980,
#     26197, 29003, 32089, 34755, 27301, 29117, 35290, 32890, 27600, 34948, 25232, 19534, 26913, 30175, 17304, 19343, 23865, 24239, 29116,
#     31008, 32066, 35547, 36784, 24347, 34590, 32460, 32461, 27549, 27627, 26464, 25097, 34575, 27280, 33280, 17616, 17967, 25401, 27859,
#     28779, 29693, 36510, 26959, 33416, 29118, 28612, 27764, 20173, 20174, 25239, 25731, 26099, 36249, 26467, 26492, 29263, 27602, 27110,
#     28502, 25806, 29443, 32068, 34164, 34753, 26102, 29291, 36628, 17795, 21036, 24993, 29836, 35164, 39142, 29222, 26428, 27822, 35963,
#     36493, 20204, 24232, 32581, 34504, 36804, 34687, 25950, 26958, 32019, 24799, 25170, 26476, 27877, 37018, 37205, 34686, 30044, 34096,
#     26707, 26266, 27995, 33009, 38676, 37066, 29023, 33415, 36610, 20606, 20742, 23346, 25240, 29705, 32850, 35271, 35163, 34574, 37519,
#     35564, 28480, 17657, 23257, 24349, 25998, 27643, 28121, 32022, 35044, 36687, 33194, 36394, 37376, 37676, 47987, 25676, 35100, 24996,
#     27861, 28999, 29208, 31007, 29246, 30174, 33007, 27170, 32884, 35585, 31445, 34748, 17793, 25677, 28253, 34808, 27646, 23906, 34809,
#     27006, 33436, 34054, 35006, 14990, 17259, 17912, 20692, 23460, 23619, 23797, 24995, 25948, 26911, 26927, 28443, 36708, 38660, 17746,
#     20144, 32716, 35322, 39087, 38600, 35584, 27908, 29666, 35197, 26512, 34324, 29002, 37077, 37677, 38257, 39001, 29777, 36749, 27551,
#     29807, 33498, 37600, 25303, 27893, 28968, 38564, 31097, 20658, 22440, 27270, 32988, 35527, 39141, 37589, 17669, 26780, 30384,
#     35349, 24966, 35106, 34086, 23535, 35246, 33468, 34287, 33281, 34734, 34097, 29115, 17325, 31945, 36250, 28525, 36805, 14972,
#     29706, 29937, 36291, 27190, 36101, 17489, 27297, 29366, 32528, 36093, 36398, 17337, 20603, 23758, 29500, 27783, 35830, 17488,
#     21581, 30602, 37687, 29607, 27793, 35809, 23891, 34795, 17425, 18030, 23281, 33363, 34401, 34709, 27882, 27011, 26380, 17886,
#     22115, 36789, 17521, 17903, 32472, 37019, 38696, 39143, 32972, 36248, 35623, 34598, 34448, 37627, 17321, 17345, 17493, 17614,
#     17712, 17857, 17869, 36985, 23776, 25752, 27334, 29413, 36462, 29845, 34431, 17579, 20618, 33012, 29534, 37487, 34397, 37330,
#     25853, 17647, 17988, 29000, 26809, 34403, 30985, 27756, 35451, 17615, 24497, 34733, 25040, 37377, 17610, 23289, 23613, 34365,
#     35582, 17611, 28466, 35008, 34708, 37596, 37650, 32055, 37604, 36677, 30385, 36939, 19533, 42701, 26001, 34896, 35941, 37664, 38584,
#     30423, 34730, 9208, 17320, 23740, 26899, 34443, 26783, 35005, 37679, 20092, 32634, 32821, 37535, 29135, 42519, 37051, 38884, 42595,
#     27778, 32632, 36692, 34526, 32958, 25521, 38250, 34402, 39165, 24341, 26716, 27405, 31440, 17486, 25717, 28684, 33425, 20143, 27693,
#     43241, 42705, 34405, 23286, 17527, 17686, 31567, 31550, 31767, 42861, 34897, 43692, 27372, 27784, 35671, 36494, 38630, 39236, 32872,
#     34464, 37094, 17787, 35007, 37680, 27491, 34688, 43220, 20418, 28625, 36300, 36352, 10025, 17494, 17713, 36095, 28566, 26027, 17733, 27988,
#     35699, 15054, 17875, 23764, 26381, 28478, 32511, 37581,66428,
#     38959, 43695, 54816, 37656, 37255, 35833, 43408, 23820, 43693, 76793, 20210, 55383, 42879, 42880, 42860, 57425, 52555, 57426, 43349, 32776, 35181, 52548, 52379, 58585, 43694, 38329)
# )a
# group by product_id,product_name,storage,
# purchase_way ,guarantee
# )a
# left join algo.algo_product_price_segment_data b
# on a.product_id=b.product_id and a.storage=b.storage
#
# """
# # and dpk.purchase_channel_name ='大陆国行（苹果系统型号M开头）'
#
# #sku+ppv组合的售卖量
# query_sku_sale_cnt="""
# select product_id,product_name,product_sku_id,storage,
# purchase_way ,guarantee ,
# count(1) as cnt
# from
# (
# SELECT
# cdp.document_item_id,
# cast(psdp.settle_amount_num as int)AS item_quotation_price_num,
#  cast(psdp.settle_document_create_date as date) as settle_list_create_date,
# cast(cdp.create_date as date) AS shop_out_date,
# cast(dp.product_id as int) product_id,
# trim(dp.product_name) as product_name,
#   dpk.product_sku_id,
# cast(dp.product_brand_id  as int) as brand_id,
# trim(dp.product_brand_name) as product_brand_name,
# cast(cdp.product_combine_level_id  as int) as level_id,
# trim(cdp.product_combine_level_name) AS product_level_name,
# sku2level.secondary_level_id,
# level.product_level_name  AS secondary_level_name,
#   substring(level.product_level_name,1,1) as level_sub,
# cdp.product_no,
# dpk.memory_name as memory ,
# dpk.memory_name as storage ,
# dpk.color_name as body_color ,
# dpk.small_model_name as small_version ,
# dpk.purchase_channel_name as purchase_way ,
# dpk.network_standard_name as guarantee ,
# dpk.network_standard_name as networks
#
# FROM dw.dw_centre_document_product cdp
#  JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no
# inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no
# JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
# inner join dim.dim_product_sku dpk
# on sku2level.secondary_sku_id=dpk.product_sku_id
# inner join dim.dim_product_level level
# on level.product_level_id=sku2level.secondary_level_id
# JOIN ods.ods_algorithm_dm_model_goods_code_property_mobile cpm  ON psdp.goods_code = cpm.goods_code
# left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no
# AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
# WHERE dp.product_category_id = 1 AND cdp.document_category_id >= 200
# AND cdp.document_item_status_id IN (201, 202, 204)
# AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
# AND psdp.quotation_document_type_id = 10
# AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
# and psdp.settle_status_id not in (-1,5,9)
# and substr(level.product_level_name,1,1) in ('A','B','C')
# and dp.product_brand_id !=52
# and psdp.product_source_id in (101,103)
# and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
# and dp.product_id in (31767, 39143, 37600, 34097, 23906, 39004, 35515, 34688, 39025, 36687, 36248, 33498, 32850, 39165, 37524, 35622,
# 35164, 38972, 36247, 23866, 36045, 33009, 39177, 37703, 34402, 34053, 37109, 37557, 35557, 34352, 39064, 36300, 37598, 34694, 34086, 27551,
#  35941, 35884, 36047, 36046, 26422, 27784, 37330, 34397, 33012, 42861, 35623, 36439, 28421, 30175, 22706, 37680, 35480, 37051, 35963, 20692,
#   35100, 37650, 34748, 17425, 38297, 27877, 34464, 29116, 34403, 37512, 24390, 22441, 17527, 36246, 27822, 36939, 37589, 28760, 35106,
#   38394, 37217, 35700, 34687, 35290, 29637, 28121, 2246,2247,17455,17457,17460,17458,17459,20079,34701,43512,43513,43511,43510,
#   39142, 39087, 38660, 38564, 38257, 37677, 37676, 37664, 37627, 37604, 37519, 37376,
#   37094, 37019, 36985, 36805, 36744, 36708, 36510, 36493, 36249, 36095, 36044, 35585, 35584, 35547, 35246, 34948, 34809, 34756, 34755,
#   34754, 34753, 34708, 34576, 34575, 34504, 34401, 34324, 34286, 34164, 33468, 33281, 32955, 32835, 32588, 32461, 32460, 32292, 32291,
#   32290, 32068, 32066, 32050, 31945, 31008, 30173, 30044, 29705, 29693, 29607, 29291, 29261, 29260, 29115, 29023, 29002, 29000, 28999,
#   28968, 28829, 28612, 28495, 28168, 27860, 27859, 27782, 27781, 27640, 27639, 27637, 27627, 27550, 27512, 27344, 27301, 27280, 27253,
#   27036, 26959, 26913, 26912, 26707, 26613, 26536, 26512, 26491, 26467, 26464, 26431, 26428, 26381, 26170, 26102, 26099, 26078, 25998,
#   25950, 25827, 25820, 25806, 25731, 25680, 25679, 25677, 25676, 25519, 25240, 25170, 25097, 24799, 24348, 24347, 24240, 24217, 23819,
#   23788, 23756, 23668, 23640, 23423, 23422, 23049, 20421, 20122, 19664, 17895, 17752, 17726, 17462, 17461, 15080,
#   9478, 15080, 17252, 17338, 17507, 17578, 17887, 17895, 17896, 19664, 19665, 20122, 20421, 22187, 22441, 22706, 23049, 23077, 23125,
#    23534, 23587, 23614, 23640, 23668, 23760, 23788, 23814, 23819, 23862, 23866, 24217, 24240, 24390, 24992, 25231, 25432, 25519, 25641,
#     25682, 25820, 25985, 26062, 26129, 26165, 26167, 26170, 26422, 26490, 26491, 26493, 26513, 26662, 27035, 27036, 27541, 28760, 29348,
#     36744, 29260, 21885, 23841, 24552, 26808, 27253, 36246, 32955, 17698, 26098, 34576, 26912, 32588, 35884, 30173, 28495, 20499, 25616,
#     25765, 34756, 35762, 27344, 34754, 20751, 21769, 22117, 32827, 34376, 32835, 27550, 36633, 23375, 25400, 25422, 26078, 26536, 27512,
#     27782, 29637, 27860, 27781, 34286, 28614, 26431, 19657, 23968, 26671, 29261, 23756, 28168, 34053, 24348, 32244, 26613, 28829, 27980,
#     26197, 29003, 32089, 34755, 27301, 29117, 35290, 32890, 27600, 34948, 25232, 19534, 26913, 30175, 17304, 19343, 23865, 24239, 29116,
#     31008, 32066, 35547, 36784, 24347, 34590, 32460, 32461, 27549, 27627, 26464, 25097, 34575, 27280, 33280, 17616, 17967, 25401, 27859,
#     28779, 29693, 36510, 26959, 33416, 29118, 28612, 27764, 20173, 20174, 25239, 25731, 26099, 36249, 26467, 26492, 29263, 27602, 27110,
#     28502, 25806, 29443, 32068, 34164, 34753, 26102, 29291, 36628, 17795, 21036, 24993, 29836, 35164, 39142, 29222, 26428, 27822, 35963,
#     36493, 20204, 24232, 32581, 34504, 36804, 34687, 25950, 26958, 32019, 24799, 25170, 26476, 27877, 37018, 37205, 34686, 30044, 34096,
#     26707, 26266, 27995, 33009, 38676, 37066, 29023, 33415, 36610, 20606, 20742, 23346, 25240, 29705, 32850, 35271, 35163, 34574, 37519,
#     35564, 28480, 17657, 23257, 24349, 25998, 27643, 28121, 32022, 35044, 36687, 33194, 36394, 37376, 37676, 47987, 25676, 35100, 24996,
#     27861, 28999, 29208, 31007, 29246, 30174, 33007, 27170, 32884, 35585, 31445, 34748, 17793, 25677, 28253, 34808, 27646, 23906, 34809,
#     27006, 33436, 34054, 35006, 14990, 17259, 17912, 20692, 23460, 23619, 23797, 24995, 25948, 26911, 26927, 28443, 36708, 38660, 17746,
#     20144, 32716, 35322, 39087, 38600, 35584, 27908, 29666, 35197, 26512, 34324, 29002, 37077, 37677, 38257, 39001, 29777, 36749, 27551,
#     29807, 33498, 37600, 25303, 27893, 28968, 38564, 31097, 20658, 22440, 27270, 32988, 35527, 39141, 37589, 17669, 26780, 30384,
#     35349, 24966, 35106, 34086, 23535, 35246, 33468, 34287, 33281, 34734, 34097, 29115, 17325, 31945, 36250, 28525, 36805, 14972,
#     29706, 29937, 36291, 27190, 36101, 17489, 27297, 29366, 32528, 36093, 36398, 17337, 20603, 23758, 29500, 27783, 35830, 17488,
#     21581, 30602, 37687, 29607, 27793, 35809, 23891, 34795, 17425, 18030, 23281, 33363, 34401, 34709, 27882, 27011, 26380, 17886,
#     22115, 36789, 17521, 17903, 32472, 37019, 38696, 39143, 32972, 36248, 35623, 34598, 34448, 37627, 17321, 17345, 17493, 17614,
#     17712, 17857, 17869, 36985, 23776, 25752, 27334, 29413, 36462, 29845, 34431, 17579, 20618, 33012, 29534, 37487, 34397, 37330,
#     25853, 17647, 17988, 29000, 26809, 34403, 30985, 27756, 35451, 17615, 24497, 34733, 25040, 37377, 17610, 23289, 23613, 34365,
#     35582, 17611, 28466, 35008, 34708, 37596, 37650, 32055, 37604, 36677, 30385, 36939, 19533, 42701, 26001, 34896, 35941, 37664, 38584,
#     30423, 34730, 9208, 17320, 23740, 26899, 34443, 26783, 35005, 37679, 20092, 32634, 32821, 37535, 29135, 42519, 37051, 38884, 42595,
#     27778, 32632, 36692, 34526, 32958, 25521, 38250, 34402, 39165, 24341, 26716, 27405, 31440, 17486, 25717, 28684, 33425, 20143, 27693,
#     43241, 42705, 34405, 23286, 17527, 17686, 31567, 31550, 31767, 42861, 34897, 43692, 27372, 27784, 35671, 36494, 38630, 39236, 32872,
#     34464, 37094, 17787, 35007, 37680, 27491, 34688, 43220, 20418, 28625, 36300, 36352, 10025, 17494, 17713, 36095, 28566, 26027, 17733, 27988,
#     35699, 15054, 17875, 23764, 26381, 28478, 32511, 37581,66428,
#     38959, 43695, 54816, 37656, 37255, 35833, 43408, 23820, 43693, 76793, 20210, 55383, 42879, 42880, 42860, 57425, 52555, 57426, 43349, 32776, 35181, 52548, 52379, 58585, 43694, 38329)
# )a
# group by product_id,product_name,product_sku_id,storage,
# purchase_way ,guarantee
# """
#
# query_level_sub_ppv_sql="""
# select product_id,product_name,level_sub,storage,
# purchase_way ,guarantee ,
# avg(item_quotation_price_num) as level_item_quotation_avg_price ,
# count(1) as level_cnt_product
# from
# (
# SELECT
# cdp.document_item_id,
# cast(psdp.settle_amount_num as int)AS item_quotation_price_num,
#  cast(psdp.settle_document_create_date as date) as settle_list_create_date,
# cast(cdp.create_date as date) AS shop_out_date,
# cast(dp.product_id as int) product_id,
# trim(dp.product_name) as product_name,
# cast(dp.product_brand_id  as int) as brand_id,
# trim(dp.product_brand_name) as product_brand_name,
# cast(cdp.product_combine_level_id  as int) as level_id,
# trim(cdp.product_combine_level_name) AS product_level_name,
# sku2level.secondary_level_id,
# level.product_level_name  AS secondary_level_name,
#   substring(level.product_level_name,1,1) as level_sub,
# cdp.product_no,
# dpk.memory_name as memory ,
# dpk.memory_name as storage ,
# dpk.color_name as body_color ,
# dpk.small_model_name as small_version ,
# dpk.purchase_channel_name as purchase_way ,
# dpk.network_standard_name as guarantee ,
# dpk.network_standard_name as networks
#
# FROM dw.dw_centre_document_product cdp
#  JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no
# inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no
# JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
# inner join dim.dim_product_sku dpk
# on sku2level.secondary_sku_id=dpk.product_sku_id
# inner join dim.dim_product_level level
# on level.product_level_id=sku2level.secondary_level_id
# JOIN ods.ods_algorithm_dm_model_goods_code_property_mobile cpm  ON psdp.goods_code = cpm.goods_code
# left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no
# AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
# WHERE dp.product_category_id = 1 AND cdp.document_category_id >= 200
# AND cdp.document_item_status_id IN (201, 202, 204)
# AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
# AND psdp.quotation_document_type_id = 10
# AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
# and psdp.settle_status_id not in (-1,5,9)
# and substr(level.product_level_name,1,1) in ('A','B','C')
# and dp.product_brand_id !=52
# and psdp.product_source_id in (101,103)
# and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
# and dp.product_id in (31767, 39143, 37600, 34097, 23906, 39004, 35515, 34688, 39025, 36687, 36248, 33498, 32850, 39165, 37524, 35622,
# 35164, 38972, 36247, 23866, 36045, 33009, 39177, 37703, 34402, 34053, 37109, 37557, 35557, 34352, 39064, 36300, 37598, 34694, 34086, 27551,
#  35941, 35884, 36047, 36046, 26422, 27784, 37330, 34397, 33012, 42861, 35623, 36439, 28421, 30175, 22706, 37680, 35480, 37051, 35963, 20692,
#   35100, 37650, 34748, 17425, 38297, 27877, 34464, 29116, 34403, 37512, 24390, 22441, 17527, 36246, 27822, 36939, 37589, 28760, 35106,
#   38394, 37217, 35700, 34687, 35290, 29637, 28121, 2246,2247,17455,17457,17460,17458,17459,20079,34701,43512,43513,43511,43510,
#   39142, 39087, 38660, 38564, 38257, 37677, 37676, 37664, 37627, 37604, 37519, 37376,
#   37094, 37019, 36985, 36805, 36744, 36708, 36510, 36493, 36249, 36095, 36044, 35585, 35584, 35547, 35246, 34948, 34809, 34756, 34755,
#   34754, 34753, 34708, 34576, 34575, 34504, 34401, 34324, 34286, 34164, 33468, 33281, 32955, 32835, 32588, 32461, 32460, 32292, 32291,
#   32290, 32068, 32066, 32050, 31945, 31008, 30173, 30044, 29705, 29693, 29607, 29291, 29261, 29260, 29115, 29023, 29002, 29000, 28999,
#   28968, 28829, 28612, 28495, 28168, 27860, 27859, 27782, 27781, 27640, 27639, 27637, 27627, 27550, 27512, 27344, 27301, 27280, 27253,
#   27036, 26959, 26913, 26912, 26707, 26613, 26536, 26512, 26491, 26467, 26464, 26431, 26428, 26381, 26170, 26102, 26099, 26078, 25998,
#   25950, 25827, 25820, 25806, 25731, 25680, 25679, 25677, 25676, 25519, 25240, 25170, 25097, 24799, 24348, 24347, 24240, 24217, 23819,
#   23788, 23756, 23668, 23640, 23423, 23422, 23049, 20421, 20122, 19664, 17895, 17752, 17726, 17462, 17461, 15080,
#   9478, 15080, 17252, 17338, 17507, 17578, 17887, 17895, 17896, 19664, 19665, 20122, 20421, 22187, 22441, 22706, 23049, 23077, 23125,
#    23534, 23587, 23614, 23640, 23668, 23760, 23788, 23814, 23819, 23862, 23866, 24217, 24240, 24390, 24992, 25231, 25432, 25519, 25641,
#     25682, 25820, 25985, 26062, 26129, 26165, 26167, 26170, 26422, 26490, 26491, 26493, 26513, 26662, 27035, 27036, 27541, 28760, 29348,
#     36744, 29260, 21885, 23841, 24552, 26808, 27253, 36246, 32955, 17698, 26098, 34576, 26912, 32588, 35884, 30173, 28495, 20499, 25616,
#     25765, 34756, 35762, 27344, 34754, 20751, 21769, 22117, 32827, 34376, 32835, 27550, 36633, 23375, 25400, 25422, 26078, 26536, 27512,
#     27782, 29637, 27860, 27781, 34286, 28614, 26431, 19657, 23968, 26671, 29261, 23756, 28168, 34053, 24348, 32244, 26613, 28829, 27980,
#     26197, 29003, 32089, 34755, 27301, 29117, 35290, 32890, 27600, 34948, 25232, 19534, 26913, 30175, 17304, 19343, 23865, 24239, 29116,
#     31008, 32066, 35547, 36784, 24347, 34590, 32460, 32461, 27549, 27627, 26464, 25097, 34575, 27280, 33280, 17616, 17967, 25401, 27859,
#     28779, 29693, 36510, 26959, 33416, 29118, 28612, 27764, 20173, 20174, 25239, 25731, 26099, 36249, 26467, 26492, 29263, 27602, 27110,
#     28502, 25806, 29443, 32068, 34164, 34753, 26102, 29291, 36628, 17795, 21036, 24993, 29836, 35164, 39142, 29222, 26428, 27822, 35963,
#     36493, 20204, 24232, 32581, 34504, 36804, 34687, 25950, 26958, 32019, 24799, 25170, 26476, 27877, 37018, 37205, 34686, 30044, 34096,
#     26707, 26266, 27995, 33009, 38676, 37066, 29023, 33415, 36610, 20606, 20742, 23346, 25240, 29705, 32850, 35271, 35163, 34574, 37519,
#     35564, 28480, 17657, 23257, 24349, 25998, 27643, 28121, 32022, 35044, 36687, 33194, 36394, 37376, 37676, 47987, 25676, 35100, 24996,
#     27861, 28999, 29208, 31007, 29246, 30174, 33007, 27170, 32884, 35585, 31445, 34748, 17793, 25677, 28253, 34808, 27646, 23906, 34809,
#     27006, 33436, 34054, 35006, 14990, 17259, 17912, 20692, 23460, 23619, 23797, 24995, 25948, 26911, 26927, 28443, 36708, 38660, 17746,
#     20144, 32716, 35322, 39087, 38600, 35584, 27908, 29666, 35197, 26512, 34324, 29002, 37077, 37677, 38257, 39001, 29777, 36749, 27551,
#     29807, 33498, 37600, 25303, 27893, 28968, 38564, 31097, 20658, 22440, 27270, 32988, 35527, 39141, 37589, 17669, 26780, 30384,
#     35349, 24966, 35106, 34086, 23535, 35246, 33468, 34287, 33281, 34734, 34097, 29115, 17325, 31945, 36250, 28525, 36805, 14972,
#     29706, 29937, 36291, 27190, 36101, 17489, 27297, 29366, 32528, 36093, 36398, 17337, 20603, 23758, 29500, 27783, 35830, 17488,
#     21581, 30602, 37687, 29607, 27793, 35809, 23891, 34795, 17425, 18030, 23281, 33363, 34401, 34709, 27882, 27011, 26380, 17886,
#     22115, 36789, 17521, 17903, 32472, 37019, 38696, 39143, 32972, 36248, 35623, 34598, 34448, 37627, 17321, 17345, 17493, 17614,
#     17712, 17857, 17869, 36985, 23776, 25752, 27334, 29413, 36462, 29845, 34431, 17579, 20618, 33012, 29534, 37487, 34397, 37330,
#     25853, 17647, 17988, 29000, 26809, 34403, 30985, 27756, 35451, 17615, 24497, 34733, 25040, 37377, 17610, 23289, 23613, 34365,
#     35582, 17611, 28466, 35008, 34708, 37596, 37650, 32055, 37604, 36677, 30385, 36939, 19533, 42701, 26001, 34896, 35941, 37664, 38584,
#     30423, 34730, 9208, 17320, 23740, 26899, 34443, 26783, 35005, 37679, 20092, 32634, 32821, 37535, 29135, 42519, 37051, 38884, 42595,
#     27778, 32632, 36692, 34526, 32958, 25521, 38250, 34402, 39165, 24341, 26716, 27405, 31440, 17486, 25717, 28684, 33425, 20143, 27693,
#     43241, 42705, 34405, 23286, 17527, 17686, 31567, 31550, 31767, 42861, 34897, 43692, 27372, 27784, 35671, 36494, 38630, 39236, 32872,
#     34464, 37094, 17787, 35007, 37680, 27491, 34688, 43220, 20418, 28625, 36300, 36352, 10025, 17494, 17713, 36095, 28566, 26027, 17733, 27988,
#     35699, 15054, 17875, 23764, 26381, 28478, 32511, 37581,66428,
#     38959, 43695, 54816, 37656, 37255, 35833, 43408, 23820, 43693, 76793, 20210, 55383, 42879, 42880, 42860, 57425, 52555, 57426, 43349, 32776, 35181, 52548, 52379, 58585, 43694, 38329)
# )a
# group by product_id,product_name,level_sub,storage,
# purchase_way ,guarantee
#
# """
#
# query_ppv_combine_all="""
# select distinct dpk.product_id,dpk.product_name,
# dpk.memory_name as storage ,
# dpk.purchase_channel_name as purchase_way ,
# dpk.network_standard_name as guarantee
# FROM  dim.dim_product_sku dpk
# JOIN dim.dim_product_sku_channel_mapping dps
# ON dpk.product_sku_id = dps.mapping_product_sku_id
# where dpk.product_category_id=1 and dpk.product_brand_id!=52  and dpk.is_product_sku_active_flag=1 and dpk.business_channel='0'
#       and dpk.if_c2b_product_flag=1 and dps.product_id in (31767, 39143, 37600, 34097, 23906, 39004, 35515, 34688, 39025, 36687, 36248, 33498, 32850, 39165, 37524, 35622,
# 35164, 38972, 36247, 23866, 36045, 33009, 39177, 37703, 34402, 34053, 37109, 37557, 35557, 34352, 39064, 36300, 37598, 34694, 34086, 27551,
#  35941, 35884, 36047, 36046, 26422, 27784, 37330, 34397, 33012, 42861, 35623, 36439, 28421, 30175, 22706, 37680, 35480, 37051, 35963, 20692,
#   35100, 37650, 34748, 17425, 38297, 27877, 34464, 29116, 34403, 37512, 24390, 22441, 17527, 36246, 27822, 36939, 37589, 28760, 35106,
#   38394, 37217, 35700, 34687, 35290, 29637, 28121, 2246,2247,17455,17457,17460,17458,17459,20079,34701,43512,43513,43511,43510,
#   39142, 39087, 38660, 38564, 38257, 37677, 37676, 37664, 37627, 37604, 37519, 37376,
#   37094, 37019, 36985, 36805, 36744, 36708, 36510, 36493, 36249, 36095, 36044, 35585, 35584, 35547, 35246, 34948, 34809, 34756, 34755,
#   34754, 34753, 34708, 34576, 34575, 34504, 34401, 34324, 34286, 34164, 33468, 33281, 32955, 32835, 32588, 32461, 32460, 32292, 32291,
#   32290, 32068, 32066, 32050, 31945, 31008, 30173, 30044, 29705, 29693, 29607, 29291, 29261, 29260, 29115, 29023, 29002, 29000, 28999,
#   28968, 28829, 28612, 28495, 28168, 27860, 27859, 27782, 27781, 27640, 27639, 27637, 27627, 27550, 27512, 27344, 27301, 27280, 27253,
#   27036, 26959, 26913, 26912, 26707, 26613, 26536, 26512, 26491, 26467, 26464, 26431, 26428, 26381, 26170, 26102, 26099, 26078, 25998,
#   25950, 25827, 25820, 25806, 25731, 25680, 25679, 25677, 25676, 25519, 25240, 25170, 25097, 24799, 24348, 24347, 24240, 24217, 23819,
#   23788, 23756, 23668, 23640, 23423, 23422, 23049, 20421, 20122, 19664, 17895, 17752, 17726, 17462, 17461, 15080,
#   9478, 15080, 17252, 17338, 17507, 17578, 17887, 17895, 17896, 19664, 19665, 20122, 20421, 22187, 22441, 22706, 23049, 23077, 23125,
#    23534, 23587, 23614, 23640, 23668, 23760, 23788, 23814, 23819, 23862, 23866, 24217, 24240, 24390, 24992, 25231, 25432, 25519, 25641,
#     25682, 25820, 25985, 26062, 26129, 26165, 26167, 26170, 26422, 26490, 26491, 26493, 26513, 26662, 27035, 27036, 27541, 28760, 29348,
#     36744, 29260, 21885, 23841, 24552, 26808, 27253, 36246, 32955, 17698, 26098, 34576, 26912, 32588, 35884, 30173, 28495, 20499, 25616,
#     25765, 34756, 35762, 27344, 34754, 20751, 21769, 22117, 32827, 34376, 32835, 27550, 36633, 23375, 25400, 25422, 26078, 26536, 27512,
#     27782, 29637, 27860, 27781, 34286, 28614, 26431, 19657, 23968, 26671, 29261, 23756, 28168, 34053, 24348, 32244, 26613, 28829, 27980,
#     26197, 29003, 32089, 34755, 27301, 29117, 35290, 32890, 27600, 34948, 25232, 19534, 26913, 30175, 17304, 19343, 23865, 24239, 29116,
#     31008, 32066, 35547, 36784, 24347, 34590, 32460, 32461, 27549, 27627, 26464, 25097, 34575, 27280, 33280, 17616, 17967, 25401, 27859,
#     28779, 29693, 36510, 26959, 33416, 29118, 28612, 27764, 20173, 20174, 25239, 25731, 26099, 36249, 26467, 26492, 29263, 27602, 27110,
#     28502, 25806, 29443, 32068, 34164, 34753, 26102, 29291, 36628, 17795, 21036, 24993, 29836, 35164, 39142, 29222, 26428, 27822, 35963,
#     36493, 20204, 24232, 32581, 34504, 36804, 34687, 25950, 26958, 32019, 24799, 25170, 26476, 27877, 37018, 37205, 34686, 30044, 34096,
#     26707, 26266, 27995, 33009, 38676, 37066, 29023, 33415, 36610, 20606, 20742, 23346, 25240, 29705, 32850, 35271, 35163, 34574, 37519,
#     35564, 28480, 17657, 23257, 24349, 25998, 27643, 28121, 32022, 35044, 36687, 33194, 36394, 37376, 37676, 47987, 25676, 35100, 24996,
#     27861, 28999, 29208, 31007, 29246, 30174, 33007, 27170, 32884, 35585, 31445, 34748, 17793, 25677, 28253, 34808, 27646, 23906, 34809,
#     27006, 33436, 34054, 35006, 14990, 17259, 17912, 20692, 23460, 23619, 23797, 24995, 25948, 26911, 26927, 28443, 36708, 38660, 17746,
#     20144, 32716, 35322, 39087, 38600, 35584, 27908, 29666, 35197, 26512, 34324, 29002, 37077, 37677, 38257, 39001, 29777, 36749, 27551,
#     29807, 33498, 37600, 25303, 27893, 28968, 38564, 31097, 20658, 22440, 27270, 32988, 35527, 39141, 37589, 17669, 26780, 30384,
#     35349, 24966, 35106, 34086, 23535, 35246, 33468, 34287, 33281, 34734, 34097, 29115, 17325, 31945, 36250, 28525, 36805, 14972,
#     29706, 29937, 36291, 27190, 36101, 17489, 27297, 29366, 32528, 36093, 36398, 17337, 20603, 23758, 29500, 27783, 35830, 17488,
#     21581, 30602, 37687, 29607, 27793, 35809, 23891, 34795, 17425, 18030, 23281, 33363, 34401, 34709, 27882, 27011, 26380, 17886,
#     22115, 36789, 17521, 17903, 32472, 37019, 38696, 39143, 32972, 36248, 35623, 34598, 34448, 37627, 17321, 17345, 17493, 17614,
#     17712, 17857, 17869, 36985, 23776, 25752, 27334, 29413, 36462, 29845, 34431, 17579, 20618, 33012, 29534, 37487, 34397, 37330,
#     25853, 17647, 17988, 29000, 26809, 34403, 30985, 27756, 35451, 17615, 24497, 34733, 25040, 37377, 17610, 23289, 23613, 34365,
#     35582, 17611, 28466, 35008, 34708, 37596, 37650, 32055, 37604, 36677, 30385, 36939, 19533, 42701, 26001, 34896, 35941, 37664, 38584,
#     30423, 34730, 9208, 17320, 23740, 26899, 34443, 26783, 35005, 37679, 20092, 32634, 32821, 37535, 29135, 42519, 37051, 38884, 42595,
#     27778, 32632, 36692, 34526, 32958, 25521, 38250, 34402, 39165, 24341, 26716, 27405, 31440, 17486, 25717, 28684, 33425, 20143, 27693,
#     43241, 42705, 34405, 23286, 17527, 17686, 31567, 31550, 31767, 42861, 34897, 43692, 27372, 27784, 35671, 36494, 38630, 39236, 32872,
#     34464, 37094, 17787, 35007, 37680, 27491, 34688, 43220, 20418, 28625, 36300, 36352, 10025, 17494, 17713, 36095, 28566, 26027, 17733, 27988,
#     35699, 15054, 17875, 23764, 26381, 28478, 32511, 37581,66428,
#     38959, 43695, 54816, 37656, 37255, 35833, 43408, 23820, 43693, 76793, 20210, 55383, 42879, 42880, 42860, 57425, 52555, 57426, 43349, 32776, 35181, 52548, 52379, 58585, 43694, 38329)
# order by 1 desc,dpk.memory_name,dpk.purchase_channel_name,dpk.network_standard_name
# """
#
#
# query_product_ppv_small_version_cnt="""
# select a.*,b.item_quotation_price_num_media as item_quotation_price_num_media_product,b.cnt as cnt_product
# from
# (
# select product_id,product_name,storage,
# purchase_way ,guarantee ,small_version,
# avg(item_quotation_price_num) as item_quotation_price_num_avg,
# min(item_quotation_price_num) as item_quotation_price_num_min,
# max(item_quotation_price_num) as item_quotation_price_num_max,
# approx_percentile(item_quotation_price_num,0.5) item_quotation_price_num_media,
# count(1) as cnt
# from
# (
# SELECT
# cdp.document_item_id,
# cast(psdp.settle_amount_num as int)AS item_quotation_price_num,
#  cast(psdp.settle_document_create_date as date) as settle_list_create_date,
# cast(cdp.create_date as date) AS shop_out_date,
# cast(dp.product_id as int) product_id,
# trim(dp.product_name) as product_name,
# cast(dp.product_brand_id  as int) as brand_id,
# trim(dp.product_brand_name) as product_brand_name,
# cast(cdp.product_combine_level_id  as int) as level_id,
# trim(cdp.product_combine_level_name) AS product_level_name,
# sku2level.secondary_level_id,
# level.product_level_name  AS secondary_level_name,
# cdp.product_no,
# dpk.memory_name as memory ,
# dpk.memory_name as storage ,
# dpk.color_name as body_color ,
# dpk.small_model_name as small_version ,
# dpk.purchase_channel_name as purchase_way ,
# dpk.network_standard_name as guarantee ,
# dpk.network_standard_name as networks
#
# FROM dw.dw_centre_document_product cdp
#  JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no
# inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no
# JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
# inner join dim.dim_product_sku dpk
# on sku2level.secondary_sku_id=dpk.product_sku_id
# inner join dim.dim_product_level level
# on level.product_level_id=sku2level.secondary_level_id
# JOIN ods.ods_algorithm_dm_model_goods_code_property_mobile cpm  ON psdp.goods_code = cpm.goods_code
# left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no
# AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
# WHERE dp.product_category_id = 1 AND cdp.document_category_id >= 200
# AND cdp.document_item_status_id IN (201, 202, 204)
# AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
# AND psdp.quotation_document_type_id = 10
# AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
# and psdp.settle_status_id not in (-1,5,9)
# and substr(level.product_level_name,1,1) in ('A','B','C')
# and dp.product_brand_id !=52
# and psdp.product_source_id in (101,103)
# and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
# and dp.product_id in (31767, 39143, 37600, 34097, 23906, 39004, 35515, 34688, 39025, 36687, 36248, 33498, 32850, 39165, 37524, 35622,
# 35164, 38972, 36247, 23866, 36045, 33009, 39177, 37703, 34402, 34053, 37109, 37557, 35557, 34352, 39064, 36300, 37598, 34694, 34086, 27551,
#  35941, 35884, 36047, 36046, 26422, 27784, 37330, 34397, 33012, 42861, 35623, 36439, 28421, 30175, 22706, 37680, 35480, 37051, 35963, 20692,
#   35100, 37650, 34748, 17425, 38297, 27877, 34464, 29116, 34403, 37512, 24390, 22441, 17527, 36246, 27822, 36939, 37589, 28760, 35106,
#   38394, 37217, 35700, 34687, 35290, 29637, 28121, 2246,2247,17455,17457,17460,17458,17459,20079,34701,43512,43513,43511,43510,
#   39142, 39087, 38660, 38564, 38257, 37677, 37676, 37664, 37627, 37604, 37519, 37376,
#   37094, 37019, 36985, 36805, 36744, 36708, 36510, 36493, 36249, 36095, 36044, 35585, 35584, 35547, 35246, 34948, 34809, 34756, 34755,
#   34754, 34753, 34708, 34576, 34575, 34504, 34401, 34324, 34286, 34164, 33468, 33281, 32955, 32835, 32588, 32461, 32460, 32292, 32291,
#   32290, 32068, 32066, 32050, 31945, 31008, 30173, 30044, 29705, 29693, 29607, 29291, 29261, 29260, 29115, 29023, 29002, 29000, 28999,
#   28968, 28829, 28612, 28495, 28168, 27860, 27859, 27782, 27781, 27640, 27639, 27637, 27627, 27550, 27512, 27344, 27301, 27280, 27253,
#   27036, 26959, 26913, 26912, 26707, 26613, 26536, 26512, 26491, 26467, 26464, 26431, 26428, 26381, 26170, 26102, 26099, 26078, 25998,
#   25950, 25827, 25820, 25806, 25731, 25680, 25679, 25677, 25676, 25519, 25240, 25170, 25097, 24799, 24348, 24347, 24240, 24217, 23819,
#   23788, 23756, 23668, 23640, 23423, 23422, 23049, 20421, 20122, 19664, 17895, 17752, 17726, 17462, 17461, 15080,
#   9478, 15080, 17252, 17338, 17507, 17578, 17887, 17895, 17896, 19664, 19665, 20122, 20421, 22187, 22441, 22706, 23049, 23077, 23125,
#    23534, 23587, 23614, 23640, 23668, 23760, 23788, 23814, 23819, 23862, 23866, 24217, 24240, 24390, 24992, 25231, 25432, 25519, 25641,
#     25682, 25820, 25985, 26062, 26129, 26165, 26167, 26170, 26422, 26490, 26491, 26493, 26513, 26662, 27035, 27036, 27541, 28760, 29348,
#     36744, 29260, 21885, 23841, 24552, 26808, 27253, 36246, 32955, 17698, 26098, 34576, 26912, 32588, 35884, 30173, 28495, 20499, 25616,
#     25765, 34756, 35762, 27344, 34754, 20751, 21769, 22117, 32827, 34376, 32835, 27550, 36633, 23375, 25400, 25422, 26078, 26536, 27512,
#     27782, 29637, 27860, 27781, 34286, 28614, 26431, 19657, 23968, 26671, 29261, 23756, 28168, 34053, 24348, 32244, 26613, 28829, 27980,
#     26197, 29003, 32089, 34755, 27301, 29117, 35290, 32890, 27600, 34948, 25232, 19534, 26913, 30175, 17304, 19343, 23865, 24239, 29116,
#     31008, 32066, 35547, 36784, 24347, 34590, 32460, 32461, 27549, 27627, 26464, 25097, 34575, 27280, 33280, 17616, 17967, 25401, 27859,
#     28779, 29693, 36510, 26959, 33416, 29118, 28612, 27764, 20173, 20174, 25239, 25731, 26099, 36249, 26467, 26492, 29263, 27602, 27110,
#     28502, 25806, 29443, 32068, 34164, 34753, 26102, 29291, 36628, 17795, 21036, 24993, 29836, 35164, 39142, 29222, 26428, 27822, 35963,
#     36493, 20204, 24232, 32581, 34504, 36804, 34687, 25950, 26958, 32019, 24799, 25170, 26476, 27877, 37018, 37205, 34686, 30044, 34096,
#     26707, 26266, 27995, 33009, 38676, 37066, 29023, 33415, 36610, 20606, 20742, 23346, 25240, 29705, 32850, 35271, 35163, 34574, 37519,
#     35564, 28480, 17657, 23257, 24349, 25998, 27643, 28121, 32022, 35044, 36687, 33194, 36394, 37376, 37676, 47987, 25676, 35100, 24996,
#     27861, 28999, 29208, 31007, 29246, 30174, 33007, 27170, 32884, 35585, 31445, 34748, 17793, 25677, 28253, 34808, 27646, 23906, 34809,
#     27006, 33436, 34054, 35006, 14990, 17259, 17912, 20692, 23460, 23619, 23797, 24995, 25948, 26911, 26927, 28443, 36708, 38660, 17746,
#     20144, 32716, 35322, 39087, 38600, 35584, 27908, 29666, 35197, 26512, 34324, 29002, 37077, 37677, 38257, 39001, 29777, 36749, 27551,
#     29807, 33498, 37600, 25303, 27893, 28968, 38564, 31097, 20658, 22440, 27270, 32988, 35527, 39141, 37589, 17669, 26780, 30384,
#     35349, 24966, 35106, 34086, 23535, 35246, 33468, 34287, 33281, 34734, 34097, 29115, 17325, 31945, 36250, 28525, 36805, 14972,
#     29706, 29937, 36291, 27190, 36101, 17489, 27297, 29366, 32528, 36093, 36398, 17337, 20603, 23758, 29500, 27783, 35830, 17488,
#     21581, 30602, 37687, 29607, 27793, 35809, 23891, 34795, 17425, 18030, 23281, 33363, 34401, 34709, 27882, 27011, 26380, 17886,
#     22115, 36789, 17521, 17903, 32472, 37019, 38696, 39143, 32972, 36248, 35623, 34598, 34448, 37627, 17321, 17345, 17493, 17614,
#     17712, 17857, 17869, 36985, 23776, 25752, 27334, 29413, 36462, 29845, 34431, 17579, 20618, 33012, 29534, 37487, 34397, 37330,
#     25853, 17647, 17988, 29000, 26809, 34403, 30985, 27756, 35451, 17615, 24497, 34733, 25040, 37377, 17610, 23289, 23613, 34365,
#     35582, 17611, 28466, 35008, 34708, 37596, 37650, 32055, 37604, 36677, 30385, 36939, 19533, 42701, 26001, 34896, 35941, 37664, 38584,
#     30423, 34730, 9208, 17320, 23740, 26899, 34443, 26783, 35005, 37679, 20092, 32634, 32821, 37535, 29135, 42519, 37051, 38884, 42595,
#     27778, 32632, 36692, 34526, 32958, 25521, 38250, 34402, 39165, 24341, 26716, 27405, 31440, 17486, 25717, 28684, 33425, 20143, 27693,
#     43241, 42705, 34405, 23286, 17527, 17686, 31567, 31550, 31767, 42861, 34897, 43692, 27372, 27784, 35671, 36494, 38630, 39236, 32872,
#     34464, 37094, 17787, 35007, 37680, 27491, 34688, 43220, 20418, 28625, 36300, 36352, 10025, 17494, 17713, 36095, 28566, 26027, 17733, 27988,
#     35699, 15054, 17875, 23764, 26381, 28478, 32511, 37581,66428,
#     38959, 43695, 54816, 37656, 37255, 35833, 43408, 23820, 43693, 76793, 20210, 55383, 42879, 42880, 42860, 57425, 52555, 57426, 43349, 32776, 35181, 52548, 52379, 58585, 43694, 38329)
# )a
# group by product_id,product_name,storage,
# purchase_way ,guarantee ,small_version
# )a
# left join algo.algo_product_price_segment_data b
# on a.product_id=b.product_id and a.storage=b.storage
#
# """
#
# BASE_LEVEL_RANK="""
# SELECT
# product_level_id as secondary_level_id,product_level_name as secondary_level_name
# FROM dim.dim_product_level a
# WHERE product_category_id = 1 AND is_product_level_active_flag = 1
# """
#
# ppv_rank_df = presto_processor.load_sql(query_ppv_rank_sql)
#
# def get_level_rank():
#     level_rank_df = presto_processor.load_sql(BASE_LEVEL_RANK)
#     return level_rank_df
#
# #获取型号 等级，ppv组合的售卖量信息
# def get_ppv_combine_sql(start1_date,start2_date,end_date):
#     ppv_level_df = presto_processor.load_sql(query_ppv_combine_sql.format(start1_date,start2_date,end_date))
#     ppv_level_df['storage'] = ppv_level_df['storage'].fillna('unknown')
#     ppv_level_df['purchase_way'] = ppv_level_df['purchase_way'].fillna('unknown')
#     ppv_level_df['guarantee'] = ppv_level_df['guarantee'].fillna('unknown')
#     ppv_level_df['ppv_combine'] = ppv_level_df['storage'] + '_' + ppv_level_df['purchase_way'] + '_' + ppv_level_df['guarantee']
#
#     ppv_cnt_df = ppv_level_df.groupby(by=['product_id','secondary_level_id','storage'])['cnt'].agg({'total_cnt': 'sum'}).reset_index()
#     ppv_level_df = ppv_level_df.merge(ppv_cnt_df,on=['product_id','secondary_level_id','storage'])
#     ppv_level_df['percent']=ppv_level_df['cnt']/ppv_level_df['total_cnt'] #型号的ppv组合占总售卖量的比率
#     ppv_level_df['percent_rank'] = ppv_level_df[['product_id','secondary_level_id','storage','percent']].groupby(by=['product_id','secondary_level_id','storage'])['percent'].rank(ascending=False, method='first')
#
#     return ppv_level_df
#
# #获取所有ppv组合的及其各个组合的得分
# def get_all_combine_data_rank_score():
#     combine_df = presto_processor.load_sql(query_ppv_combine_all)
#     combine_df['storage'] = combine_df['storage'].fillna('unknown')
#     combine_df['purchase_way'] = combine_df['purchase_way'].fillna('unknown')
#     combine_df['guarantee'] = combine_df['guarantee'].fillna('unknown')
#     # ppv_rank_df = presto_processor.load_sql(query_ppv_rank_sql)
#
#     combine_df = combine_df.merge(ppv_rank_df.loc[ppv_rank_df.column_name == 'storage', ['column_value_name', 'ppv_rank', 'low_ppv_rank']],
#                       how='left', left_on='storage', right_on='column_value_name')
#     combine_df = combine_df.rename(columns={"ppv_rank": "storage_rank","low_ppv_rank": "storage_low_rank"})
#     combine_df = combine_df.merge(ppv_rank_df.loc[ppv_rank_df.column_name == 'purchase_way', ['column_value_name', 'ppv_rank', 'low_ppv_rank']],
#                       how='left', left_on='purchase_way', right_on='column_value_name')
#     combine_df = combine_df.rename(columns={"ppv_rank": "purchase_way_rank","low_ppv_rank": "purchase_way_low_rank"})
#     combine_df = combine_df.merge(ppv_rank_df.loc[ppv_rank_df.column_name == 'guarantee', ['column_value_name', 'ppv_rank', 'low_ppv_rank']],
#                       how='left', left_on='guarantee', right_on='column_value_name')
#     combine_df = combine_df.rename(columns={"ppv_rank": "guarantee_rank","low_ppv_rank": "guarantee_low_rank"})
#
#     combine_df['storage_rank']=combine_df['storage_rank'].fillna(100)
#     combine_df['storage_low_rank']=combine_df['storage_low_rank'].fillna(100)
#     combine_df['purchase_way_rank']=combine_df['purchase_way_rank'].fillna(100)
#     combine_df['purchase_way_low_rank']=combine_df['purchase_way_low_rank'].fillna(100)
#     combine_df['guarantee_rank']=combine_df['guarantee_rank'].fillna(100)
#     combine_df['guarantee_low_rank']=combine_df['guarantee_low_rank'].fillna(100)
#     # combine_df['small_version_rank']=combine_df['small_version_rank'].fillna(100)
#     # combine_df['small_version_rank_rate'] = combine_df['small_version_rank']*1.00000/100
#     combine_df['rank_score'] = combine_df['storage_rank'] * combine_df['purchase_way_rank'] * combine_df['guarantee_rank']
#     combine_df['rank_low_score'] = combine_df['storage_low_rank'] * combine_df['purchase_way_low_rank'] * combine_df['guarantee_low_rank']
#     combine_df.loc[combine_df.product_id.isin([17461, 17462, 17726, 17752]), 'rank_score'] = combine_df.loc[combine_df.product_id.isin([17461, 17462, 17726, 17752]), 'rank_low_score']
#
#     combine_df['ppv_combine'] = combine_df['storage'] + '_' + combine_df['purchase_way'] + '_' + combine_df['guarantee']
#
#     return  combine_df
#
# # 获取ppv的rank score数据
# def get_product_base_ppv_fun(combine_df,start1_date,start2_date,end_date):
#     df = presto_processor.load_sql(query_product_ppv_cnt.format(start1_date,start2_date,end_date))
#     #聚合ppv
#     df['storage']=df['storage'].fillna('unknown')
#     df['purchase_way']=df['purchase_way'].fillna('unknown')
#     df['guarantee']=df['guarantee'].fillna('unknown')
#
#     df['ppv_combine'] = df['storage'] + '_' + df['purchase_way'] + '_' + df['guarantee']
#     df['ppv_base_rank'] = df[['product_id','storage','purchase_way','guarantee','ppv_combine','cnt']].groupby(by=['product_id','storage'])['cnt'].rank(ascending=False, method='first')
#
#     # 获取ppv的rank score数据
#     df = df.merge(combine_df[['product_id','ppv_combine','rank_score','rank_low_score']],on=['product_id','ppv_combine'])
#     df =df.rename(columns={"rank_score": "rank_score_base","rank_low_score": "rank_low_score_base"})
#
#     ret_df = df.loc[df.ppv_base_rank==1,['product_id','ppv_combine','storage','cnt','ppv_base_rank','rank_score_base','rank_low_score_base']] #确保有竟得的组合中留下那些出货量最多的组合
#     return ret_df
#
# def query_level_sub_ppv_price(start1_date,start2_date,end_date):
#     df = presto_processor.load_sql(query_level_sub_ppv_sql.format(start1_date,start2_date,end_date))
#     df['storage']=df['storage'].fillna('unknown')
#     df['purchase_way']=df['purchase_way'].fillna('unknown')
#     df['guarantee']=df['guarantee'].fillna('unknown')
#
#     df['ppv_combine'] = df['storage'] + '_' + df['purchase_way'] + '_' + df['guarantee']
#     return df